package com.sepro.tim.database.DAO;

import java.sql.Time;
import java.util.Vector;

import com.sepro.tim.database.DTO.DTO_TaskTimeTable;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DAO_TaskTimeTable extends SQLiteOpenHelper{
	
	private SQLiteDatabase gDatabase;
	
//	private static final String gTaskTimeTableID = "TaskTimeTableID";
//	
//	private static final String gTaskTimeTableName = "TaskTimeTableName";
//	
//	private static final String gTaskTimeTableDescription = "TaskTimeTableDescription";
//	
//	private static final String gTaskTimeTableDayOfWeek = "0";
//	
//	private static final String gTaskTimeTableAvatar = "TaskTimeTableAvatar";
//	
//	private static final String gTimeTableID = "TimeTableID";
//	
//	private static final String gTaskTimeTableStartTime = "12:12:12";
//	
//	private static final String gTaskTimeTableLongTime = "45";
	
	private static final String TABLE_NAME = "TASK_TIME_TABLE";
	
	private static final String DATABASE_CREATE = "CREATE TABLE [" + TABLE_NAME + "] (" +
			"[TaskTimeTableID] VARCHAR(10)  NOT NULL PRIMARY KEY," +
			"[TaskTimeTableName] VARCHAR(100)  NOT NULL," +
			"[TaskTimeTableDescription] NVARCHAR(300)  NULL," +
			"[TaskTimeTableDayOfWeek] NUMERIC  NOT NULL," +
			"[TaskTimeTableAvatar] VARCHAR(50)  NULL," +
			"[TimeTableID] VARCHAR(10)  NOT NULL," +
			"[TaskTimeTableStartTime] TIME  NOT NULL," +
			"[TaskTimeTableLongTime] INTEGER  NOT NULL)";

	public DAO_TaskTimeTable(Context context, String DATABASE_NAME,
			CursorFactory factory, int DATABASE_VERSION) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION);
		
		gDatabase = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, factory);
		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(DAO_TaskTimeTable.class.getName(), "Upgrading database from version " + oldVersion + "to" 
				+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);
		onCreate(db);
	}
	
	public void openWriteDatabase() throws SQLException{
		gDatabase = this.getWritableDatabase();
	}
	
	public void openReadDatabase() throws SQLException{
		gDatabase = this.getReadableDatabase();
	}
	
	public void closeDatabase() throws SQLException{
		gDatabase.close();
	}
	
	//them mot TaskTimeTable vao trong database
	public void addTaskTimeTable(DTO_TaskTimeTable mTaskTimeTable) {
		
		this.openWriteDatabase();
		
		String sql = "INSERT INTO " + TABLE_NAME + "(TaskTimeTableID,TaskTimeTableName,TaskTimeTableDescription," +
				"TaskTimeTableDayOfWeek,TaskTimeTableAvatar,TimeTableID,TaskTimeTableStartTime,TaskTimeTableLongTime) VALUES(";
		
		sql += "'" + mTaskTimeTable.getgTaskTimeTableID().trim() + "',";
		sql += "'" + mTaskTimeTable.getgTaskTimeTableName().trim() + "',";
		sql += "'" + mTaskTimeTable.getgTaskTimeTableDescription().trim() + "',";
		sql += mTaskTimeTable.getgTaskTimeTableDayOfWeek() + ",";
		sql += "'" + mTaskTimeTable.getgTaskTimeTableAvatar().trim() + "',";
		sql += "'" + mTaskTimeTable.getgTimeTableID().trim() + "',";
		sql += mTaskTimeTable.getgTaskTimeTableStartTime().toString() + ",";
		sql += mTaskTimeTable.getgTaskTimeTableLongTime() + ")";
		
		gDatabase.execSQL(sql);
		
//		ContentValues ctValues = new ContentValues();
		
//		ctValues.put(gTaskTimeTableID, mTaskTime.getgTaskTimeTableID());
//
//		ctValues.put(gTaskTimeTableName, mTaskTime.getgTaskTimeTableName());
//		
//		ctValues.put(gTaskTimeTableDescription, mTaskTime.getgTaskTimeTableDescription());
//		
//		ctValues.put(gTaskTimeTableDayOfWeek, String.valueOf(mTaskTime.getgTaskTimeTableDayOfWeek()));
//		
//		ctValues.put(gTaskTimeTableAvatar, mTaskTime.getgTaskTimeTableAvatar());
//		
//		ctValues.put(gTimeTableID, mTaskTime.getgTimeTableID());
//		
//		ctValues.put(gTaskTimeTableStartTime, mTaskTime.getgTaskTimeTableStartTime().toString());
//		
//		ctValues.put(gTaskTimeTableLongTime, String.valueOf(mTaskTime.getgTaskTimeTableLongTime()));
		
//		gDatabase.insert(TABLE_NAME, null, ctValues);
		
		this.closeDatabase();
	}
	
	//cap nhat mot TaskTimeTable vao trong database
	public void updateTaskTimeTable(DTO_TaskTimeTable mTaskTimeTable) {
		
		this.openWriteDatabase();
		
		String sql = "UPDATE " + TABLE_NAME + " SET ";
		
		sql += "TaskTimeTableName = '" + mTaskTimeTable.getgTaskTimeTableName().trim() + "',";
		sql += "TaskTimeTableDescription = '" + mTaskTimeTable.getgTaskTimeTableDescription().trim() + "',";
		sql += "TaskTimeTableDayOfWeek = " + mTaskTimeTable.getgTaskTimeTableDayOfWeek() + ",";
		sql += "TaskTimeTableAvatar = '" + mTaskTimeTable.getgTaskTimeTableAvatar().trim() +"',";
		sql += "TimeTableID = '" + mTaskTimeTable.getgTimeTableID().trim() + "',";
		sql += "TaskTimeTableStartTime = " + mTaskTimeTable.getgTaskTimeTableStartTime().toString() + ",";
		sql += "TaskTimeTableLongTime = " +mTaskTimeTable.getgTaskTimeTableLongTime() + " ";
		
		sql += "WHERE TaskTimeTableID = '" + mTaskTimeTable.getgTaskTimeTableID().trim() + "'";
		
		gDatabase.execSQL(sql);
		
//		ContentValues ctValues = new ContentValues();
//		
//		ctValues.put(gTaskTimeTableID, mTaskTime.getgTaskTimeTableID());
//
//		ctValues.put(gTaskTimeTableName, mTaskTime.getgTaskTimeTableName());
//		
//		ctValues.put(gTaskTimeTableDescription, mTaskTime.getgTaskTimeTableDescription());
//		
//		ctValues.put(gTaskTimeTableDayOfWeek, String.valueOf(mTaskTime.getgTaskTimeTableDayOfWeek()));
//		
//		ctValues.put(gTaskTimeTableAvatar, mTaskTime.getgTaskTimeTableAvatar());
//		
//		ctValues.put(gTimeTableID, mTaskTime.getgTimeTableID());
//		
//		ctValues.put(gTaskTimeTableStartTime, mTaskTime.getgTaskTimeTableStartTime().toString());
//		
//		ctValues.put(gTaskTimeTableLongTime, String.valueOf(mTaskTime.getgTaskTimeTableLongTime()));
//		
//		gDatabase.update(TABLE_NAME, ctValues, gTaskTimeTableID + "=?",
//				new String[]{String.valueOf(mTaskTime.getgTaskTimeTableID())});
		
		this.closeDatabase();
		
	}
	
	//xoaa mot TaskTimeTable theo ma id
	public void deleteTaskTimeTable(String mTaskTimeTableID) {
		
		this.openWriteDatabase();
		
		String sql ="DELETE FROM " + TABLE_NAME + " ";
		sql += "WHERE TaskTimeTableID = '" + mTaskTimeTableID.trim() + "'";
		
		gDatabase.execSQL(sql);
		
		this.closeDatabase();
		
	}
	
	public void deleteOfTimeTable(String mTimeTableID) {
		
		this.openWriteDatabase();
		
		String sql ="DELETE FROM " + TABLE_NAME + " ";
		sql += "WHERE TimeTableID = '" + mTimeTableID.trim() + "'";
		
		gDatabase.execSQL(sql);
		
		this.closeDatabase();
		
	}
	
	//search theo TaskTimeTableName
	public Vector<DTO_TaskTimeTable> searchTaskTimeName(String mTaskTimeName) {
		
		this.openReadDatabase();
		
		Vector<DTO_TaskTimeTable> list = new Vector<DTO_TaskTimeTable>();
		
		DTO_TaskTimeTable mTaskTimeTable = new DTO_TaskTimeTable();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE TaskTimeTableName LIKE '%" + mTaskTimeName.trim() + "%'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTaskTimeTable.setgTimeTableID(mCursor.getString(0).trim());
				mTaskTimeTable.setgTaskTimeTableName(mCursor.getString(1).trim());
				mTaskTimeTable.setgTaskTimeTableDescription(mCursor.getString(2).trim());
				mTaskTimeTable.setgTaskTimeTableDayOfWeek(mCursor.getInt(3));
				mTaskTimeTable.setgTaskTimeTableAvatar(mCursor.getString(4).trim());
				mTaskTimeTable.setgTimeTableID(mCursor.getString(5).trim());
				mTaskTimeTable.setgTaskTimeTableStartTime(new Time(mCursor.getLong(6)));
				mTaskTimeTable.setgTaskTimeTableLongTime(mCursor.getInt(7));
				
				list.add(mTaskTimeTable);
				
			} while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
	
	//search theo TaskTimeTableDescrip
	public Vector<DTO_TaskTimeTable> searchTaskTimeDescrip(String mTaskTimeDescrip) {
			
		this.openReadDatabase();
			
		Vector<DTO_TaskTimeTable> list = new Vector<DTO_TaskTimeTable>();
			
		DTO_TaskTimeTable mTaskTimeTable = new DTO_TaskTimeTable();
			
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE TaskTimeTableDescription LIKE '%" + mTaskTimeDescrip.trim() + "%'";
			
		Cursor mCursor = gDatabase.rawQuery(sql, null);
			
		if(mCursor.moveToFirst())
		{
			do
			{
				mTaskTimeTable.setgTimeTableID(mCursor.getString(0).trim());
				mTaskTimeTable.setgTaskTimeTableName(mCursor.getString(1).trim());
				mTaskTimeTable.setgTaskTimeTableDescription(mCursor.getString(2).trim());
				mTaskTimeTable.setgTaskTimeTableDayOfWeek(mCursor.getInt(3));
				mTaskTimeTable.setgTaskTimeTableAvatar(mCursor.getString(4).trim());
				mTaskTimeTable.setgTimeTableID(mCursor.getString(5).trim());
				mTaskTimeTable.setgTaskTimeTableStartTime(new Time(mCursor.getLong(6)));
				mTaskTimeTable.setgTaskTimeTableLongTime(mCursor.getInt(7));
					
				list.add(mTaskTimeTable);
					
			} while(mCursor.moveToNext());
				
			this.closeDatabase();
				
			return list;
				
		}
		else {
				
			this.closeDatabase();
				
			return null;
		}
	}
	
	//tim kiem theo DayOfWeek
	public Vector<DTO_TaskTimeTable> searchDayOfWeek(int mDayOfWeek) {
		
		this.openReadDatabase();
			
		Vector<DTO_TaskTimeTable> list = new Vector<DTO_TaskTimeTable>();
			
		DTO_TaskTimeTable mTaskTimeTable = new DTO_TaskTimeTable();
			
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE TaskTimeTableDayOfWeek = " + mDayOfWeek;
			
		Cursor mCursor = gDatabase.rawQuery(sql, null);
			
		if(mCursor.moveToFirst())
		{
			do
			{
				mTaskTimeTable.setgTimeTableID(mCursor.getString(0).trim());
				mTaskTimeTable.setgTaskTimeTableName(mCursor.getString(1).trim());
				mTaskTimeTable.setgTaskTimeTableDescription(mCursor.getString(2).trim());
				mTaskTimeTable.setgTaskTimeTableDayOfWeek(mCursor.getInt(3));
				mTaskTimeTable.setgTaskTimeTableAvatar(mCursor.getString(4).trim());
				mTaskTimeTable.setgTimeTableID(mCursor.getString(5).trim());
				mTaskTimeTable.setgTaskTimeTableStartTime(new Time(mCursor.getLong(6)));
				mTaskTimeTable.setgTaskTimeTableLongTime(mCursor.getInt(7));
					
				list.add(mTaskTimeTable);
					
			} while(mCursor.moveToNext());
				
			this.closeDatabase();
				
			return list;
				
		}
		else {
				
			this.closeDatabase();
				
			return null;
		}
	}
	
	//lay TaskTimeTable theo ma ID
	public DTO_TaskTimeTable getTaskTimeTable(String mTaskTimeTableID) {

		this.openReadDatabase();
		
		DTO_TaskTimeTable mTaskTimeTable = new DTO_TaskTimeTable();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " " +
				"WHERE TaskTimeTableID = '" + mTaskTimeTableID.trim() + "'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			mTaskTimeTable.setgTimeTableID(mCursor.getString(0).trim());
			mTaskTimeTable.setgTaskTimeTableName(mCursor.getString(1).trim());
			mTaskTimeTable.setgTaskTimeTableDescription(mCursor.getString(2).trim());
			mTaskTimeTable.setgTaskTimeTableDayOfWeek(mCursor.getInt(3));
			mTaskTimeTable.setgTaskTimeTableAvatar(mCursor.getString(4).trim());
			mTaskTimeTable.setgTimeTableID(mCursor.getString(5).trim());
			mTaskTimeTable.setgTaskTimeTableStartTime(new Time(mCursor.getLong(6)));
			mTaskTimeTable.setgTaskTimeTableLongTime(mCursor.getInt(7));
			
			this.closeDatabase();
			return mTaskTimeTable;		
		}
		else {
			this.closeDatabase();
			return null;
		}
	}
	
	
	//lay cac TaskTimeTable theo TimeTableID
	public Vector<DTO_TaskTimeTable> getOfTimeTable(String mTimeTableID) {

		this.openReadDatabase();
		
		Vector<DTO_TaskTimeTable> list = new Vector<DTO_TaskTimeTable>();
		
		DTO_TaskTimeTable mTaskTimeTable = new DTO_TaskTimeTable();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE TimeTableID = '" + mTimeTableID.trim() + "'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTaskTimeTable.setgTimeTableID(mCursor.getString(0).trim());
				mTaskTimeTable.setgTaskTimeTableName(mCursor.getString(1).trim());
				mTaskTimeTable.setgTaskTimeTableDescription(mCursor.getString(2).trim());
				mTaskTimeTable.setgTaskTimeTableDayOfWeek(mCursor.getInt(3));
				mTaskTimeTable.setgTaskTimeTableAvatar(mCursor.getString(4).trim());
				mTaskTimeTable.setgTimeTableID(mCursor.getString(5).trim());
				mTaskTimeTable.setgTaskTimeTableStartTime(new Time(mCursor.getLong(6)));
				mTaskTimeTable.setgTaskTimeTableLongTime(mCursor.getInt(7));
				
				list.add(mTaskTimeTable);
				
			} while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
	
	
	//lay tat ca cac TaskTimeTable
	public Vector<DTO_TaskTimeTable> getAllTaskTimeTable() {

		this.openReadDatabase();
		
		Vector<DTO_TaskTimeTable> list = new Vector<DTO_TaskTimeTable>();
		
		DTO_TaskTimeTable mTaskTimeTable = new DTO_TaskTimeTable();
		
		String sql = "SELECT * FROM " + TABLE_NAME;
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTaskTimeTable.setgTimeTableID(mCursor.getString(0).trim());
				mTaskTimeTable.setgTaskTimeTableName(mCursor.getString(1).trim());
				mTaskTimeTable.setgTaskTimeTableDescription(mCursor.getString(2).trim());
				mTaskTimeTable.setgTaskTimeTableDayOfWeek(mCursor.getInt(3));
				mTaskTimeTable.setgTaskTimeTableAvatar(mCursor.getString(4).trim());
				mTaskTimeTable.setgTimeTableID(mCursor.getString(5).trim());
				mTaskTimeTable.setgTaskTimeTableStartTime(new Time(mCursor.getLong(6)));
				mTaskTimeTable.setgTaskTimeTableLongTime(mCursor.getInt(7));
				
				list.add(mTaskTimeTable);
				
			} while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
}
